<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page trimDirectiveWhitespaces="true"%>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%@ page import = "java.sql.PreparedStatement" %>
<% 
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if(!"admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=system_select.jsp"> 	
	<%}
			
	
		

	//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	




	System.out.println("admin_lecturer_add_normal_background Active");
	
	
	java.util.Date dt = new java.util.Date();

	java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

	String date_now = sdf.format(dt);
	

	String personal_id =  request.getParameter("personal_id");
	String title_th =  request.getParameter("title_th");
	String name_th =  request.getParameter("name_th");	
	String surname_th =  request.getParameter("surname_th");
	String name_en =  request.getParameter("name_en");	
	String surname_en =  request.getParameter("surname_en");
	String gender =  request.getParameter("gender");
	String marital_status = request.getParameter("marital_status"); //null
	String address =  request.getParameter("address"); //null
	String tel =  request.getParameter("tel"); //null'
	String email = request.getParameter("email");//null
	String facebook = request.getParameter("facebook");//null

	String department_id_sel = request.getParameter("department_id");
	String edu_pos_id = request.getParameter("edu_pos");
	String manage_pos_id = request.getParameter("manage_pos");
	
	if(manage_pos_id.equals("no"))
	{
		manage_pos_id = null;
	}
	
	String faculty_id = "1";
	String date_of_birth_in = request.getParameter("date_of_birth");
	String status = request.getParameter("status");
	
	
	
	String full_name_id = "";
	Date birth_date_dt  = sdf.parse(date_of_birth_in);

	
	String birth_date = sdf.format(birth_date_dt);
	
	System.out.println("date_now:"+date_now.toString());

	System.out.println("birth_date:"+birth_date);
	String dirty = "N";
	
	
	String key_index ="";
	String title_en = null;
	
    switch(title_th){
	
		case "นาย" : title_en ="Mr."; break;
		case "นางสาว" : title_en ="Miss"; break;
		case "นาง" : title_en="Mrs."; break;
	}
	
    System.out.println("Phase1 Parameter");
    System.out.println("name_th:"+name_th);
	System.out.println("surname_th:"+surname_th);
	String key_index_temp="";
	
	 ///////////////////Phase 1 // Check Existing data in People table 
	ResultSet rs_people =	connect.createStatement().executeQuery(" SELECT P.index "
													+ " FROM people P, full_name N "
													+ " WHERE P.index=N.index "
													+ " AND N.name_th='"+name_th+"'"
													+ " AND N.surname_th='"+surname_th+"'");
    
		


	rs_people.last();
	if(rs_people.getRow() == 1) {	
		System.out.println("This people already exist in People Table");
		key_index = rs_people.getString("index");
		System.out.println("Phase 2");
		System.out.println("Found in people table with index:"+key_index);
		System.out.println("edu_position_id:"+edu_pos_id+":");
		
		
		///////////Phase 5 Add Lecturer
		String sql_cmd_phase_5 = " INSERT INTO `lecturer`(`index`,`faculty_id`,`department_id`,"+
				" `status`, `education_position_id`,`management_position_id`)"+
				" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
				" '"+status+"', "+edu_pos_id+", "+manage_pos_id+")";
		try{
			
			System.out.println("key_index:"+key_index);
			System.out.println("faculty_id:"+faculty_id);
			System.out.println("department_id:"+department_id_sel);
			System.out.println("status:"+status);
			System.out.println("edu_pos_id:"+edu_pos_id);
			System.out.println("man_pos_id:"+manage_pos_id);
			
			String sql = " INSERT INTO `lecturer`(`index`,`faculty_id`,`department_id`,"+
					" `status`, `education_position_id`,`management_position_id`)"+
					" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
					" '"+status+"', "+edu_pos_id+", "+manage_pos_id+")";
			
			connect.createStatement().executeUpdate(sql);
			
			// log file
			String log = "admin add lecturer";						
			Log.writeAdminFile(log);
			Log.writeAdminFile(sql);
					
			System.out.println("Phase 5 Success");
		
					
			}catch(Exception nm){
			
				System.out.println("Phase 5 error:"+nm);
				dirty="Y";
				
															
			}
			///////////////////End Phase 5		
	
			
			
	}
	else{
		System.out.println("Not Existing in People Table");
		
		
		  	key_index = KeyGen.genTimeStamp();
			
			/////////////////////Phase A // Add full_name to full_name table
			System.out.println("Phase A");
			System.out.println("key_index:"+key_index);
			System.out.println("Data before create full_name query");
			System.out.println(personal_id);
			System.out.println(title_th);
			System.out.println(title_en);
			System.out.println(name_th);
			System.out.println(name_en);
			System.out.println(surname_th);
			System.out.println(surname_en);
			System.out.println("gender:"+gender+":");
			System.out.println(date_now.toString());
			
			try{
				String sql = " INSERT INTO `full_name`(`index`,"+
						   " `title_th`, `title_en`, `name_th`, `name_en`, `surname_th`, "+
						   " `surname_en`, `date`) "+
						   " VALUES ('"+key_index+"',"+"'"+title_th+"', " +"'"+title_en+"', "+
						   " '"+name_th+"', "+"'"+name_en+"', "+ "'"+surname_th+"', "+"'"+surname_en+"', "+
						   " '"+date_now+"')";
				
				PreparedStatement pstmt = connect.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
				pstmt.executeUpdate();  
				ResultSet keys = pstmt.getGeneratedKeys();    
				keys.next();  
				int key = keys.getInt(1);
				
				 full_name_id = Integer.toString(key);
				 
				// log file
				String log = "admin add lecturer";						
				Log.writeAdminFile(log);
				Log.writeAdminFile(sql);
					
				System.out.println("Phase A Success with full_name_id :"+ key);
				
			
			}catch(Exception e){
				System.out.println("Error At Phase A Add Full_name:"+e);
				dirty="Y";
				
			
			} 
			////////////////////////////// End Phase A
			
			
			
			
			
			////////////// Phase B Add to people table 
			System.out.println("Data before Add People Table");	
			System.out.println("key_index:"+key_index);
			
			System.out.println("ful_name_id:"+full_name_id);
			System.out.println("gender:"+gender);
			
			System.out.println("date_of_birth"+birth_date.toString());
			System.out.println("phonenumber"+tel);
			System.out.println("address"+address);
			System.out.println("email"+email);
			System.out.println("facebook"+facebook);
			
			
		try{
			String sql_cmd = " INSERT INTO "+"`people`(`index`, `full_name_id`,"+
					   " `gender`, `date_of_birth`, `phone_number`,"+
					   " `address`, `email`, `facebook`)"+
					   " VALUES ('"+key_index+"', '"+full_name_id+"', '"+gender+"', '"+
					    birth_date+"', '"+tel+"', "+
					   " '"+address+"', '"+email+"', '"+facebook+"')";
			
			System.out.println("sql_cmd:"+sql_cmd);
			
			
			connect.createStatement().executeUpdate(sql_cmd);
			
			// log file
			Log.writeAdminFile(sql_cmd);
			
			System.out.println("Phase B Success");
			try{
				///////////Phase F Add Lecturer
				System.out.println("key_index:"+key_index);
				System.out.println("faculty_id:"+faculty_id);
				System.out.println("department_id:"+department_id_sel);
				System.out.println("status:"+status);
				System.out.println("edu_pos_id:"+edu_pos_id);
				System.out.println("man_pos_id:"+manage_pos_id);
				
				String sql = " INSERT INTO `lecturer`(`index`,`faculty_id`,`department_id`,"+
						" `status`, `education_position_id`,`management_position_id`)"+
						" VALUES ('"+key_index+"', "+faculty_id+", "+department_id_sel+","+
						" '"+status+"', "+edu_pos_id+", "+manage_pos_id+")";
				
				connect.createStatement().executeUpdate(sql);
					
				// log file
				Log.writeAdminFile(sql);
				
				System.out.println("Phase F Success");

						
				}catch(Exception nm){
				
					System.out.println("Phase F error:"+nm);
					dirty="Y";
																
				}
				///////////////////End Phase F	////////////////////	
		
			
		}catch(Exception y){
			System.out.println("Error At Phase B Add People:"+y);
			dirty="Y";
				
		}////////////////End Phase B
		
		

		if(("N").equals(dirty)){
			
			out.print("success");
		}else{
			out.println("fail");
		}
			
		
		
		
	}//end else not existing people
	
	
	connect.close();
	
	
	
	
	
	
	
	
%>